package org.hgvc.webapp.ets.spring.dao;

import java.util.List;

import org.hgvc.webapp.ets.bo.Agreement;
import org.hgvc.webapp.ets.constant.AgreementConstant;
import org.hgvc.webapp.ets.constant.CommonConstant;
import org.hibernate.Query;
import org.jocsb.base.AbstractHibernateDao;

public class AgreementHbmDao extends AbstractHibernateDao {

	public List<Agreement> getAgreementList() throws Exception {
		StringBuffer str = new StringBuffer();
		str.append(" select agreement from Agreement agreement where agreement.isDeleted=:value");

		Query query = getSession().createQuery(str.toString());
		query.setParameter("value", CommonConstant.NOT_DELETED);

		return (List<Agreement>) query.list();
	}

	public double getTotalCommPaid(String agreementId) {
		StringBuffer str = new StringBuffer();
		str.append(" select sum(a.paid) from AgentCommission a " + " where a.agreement.agreementId=:agreementId ");

		Query query = getSession().createQuery(str.toString());
		query.setParameter("agreementId", agreementId);
		if (query.list().get(0) == null)
			return 0;
		return new Double(query.list().get(0) + "");
	}

	public List<Agreement> getAgreementForCommission(String agentId, int month, int year) throws Exception {
		StringBuffer str = new StringBuffer();
		str
				.append(" select agreement from Agreement agreement where agreement.isDeleted=:value and "
						+ " agreement.member.agent.agentId=:agentId and (agreement.status=:status or (agreement.status=:terminate and agreement.affectCommission='Y'))"
						+
						// " and agreement.agentCommissionCost!=0 " +
						" and month(agreement.agreetmentDate) = :month "
						+ " and year (agreement.agreetmentDate) = :year ");

		Query query = getSession().createQuery(str.toString());
		query.setParameter("value", CommonConstant.NOT_DELETED);
		query.setParameter("agentId", agentId);
		query.setParameter("status", AgreementConstant.AGREEMENT_STATUS_ACK);
		query.setParameter("terminate", AgreementConstant.AGREEMENT_STATUS_TERMINATED);
		query.setParameter("month", month);
		query.setParameter("year", year);
		return (List<Agreement>) query.list();
	}

	public List<String> getAgreementsTerminateMonthYear(String agentId, int year, int month) throws Exception {
		StringBuffer str = new StringBuffer();
		str
				.append(" select distinct( cast( month(agreement.agreetmentDate) as string) || ';' || cast(year(agreement.agreetmentDate) as string ) || ';' || cast( agreement.agentCommissionPercentage as string )) from Agreement agreement "
						+ " where agreement.isDeleted=:value and agreement.status=:terminate"
						+ " and agreement.affectQuota='Y' "
						+ " and month(agreement.agreementTerminateDate) = :month "
						+ " and year (agreement.agreementTerminateDate) = :year "
						+ " and  agreement.member.agent.agentId = :agentId ");

		Query query = getSession().createQuery(str.toString());
		query.setParameter("value", CommonConstant.NOT_DELETED);
		query.setParameter("terminate", AgreementConstant.AGREEMENT_STATUS_TERMINATED);
		query.setParameter("agentId", agentId);
		query.setParameter("month", month);
		query.setParameter("year", year);
		return (List<String>) query.list();
	}

	public List<String> getNewAgreementMonthYear(String agentId, int year, int month) throws Exception {
		StringBuffer str = new StringBuffer();
		str
				.append(" select distinct( cast( month(agreement.agreetmentDate) as string) || ';' ||  cast( year(agreement.agreetmentDate) as string )"
						+ " || ';' || cast( agreement.agentCommissionPercentage as string ) ) from Agreement agreement "
						+ " where agreement.isDeleted=:value and agreement.status=:status"
						+ "  and month(agreement.agreetmentDate) = :month "
						+ " and year (agreement.agreetmentDate) = :year "
						+ " and  agreement.member.agent.agentId = :agentId ");
		Query query = getSession().createQuery(str.toString());
		query.setParameter("value", CommonConstant.NOT_DELETED);
		query.setParameter("agentId", agentId);
		query.setParameter("month", month);
		query.setParameter("status", AgreementConstant.AGREEMENT_STATUS_ACK);// TODO confirm whether ack is what? i
																				// want active agreement
		query.setParameter("year", year);

		return (List<String>) query.list();
	}

	public double getTotalAgreementPrice(String agentId, int year, int month) throws Exception {
		StringBuffer str = new StringBuffer();
		// TODO agreement.fullmembershippayment just work for membership change. register new member did not fill in
		// here. so now just use membership.membershipFee
		str
				.append(" select sum(agreement.membership.membershipFee) from Agreement agreement "
						+ " where agreement.isDeleted=:value and "
						+ " ( agreement.status=:ackstatus or (agreement.status=:terminatestatus and	agreement.affectQuota='N'))"
						+ "  and month(agreement.agreetmentDate) = :month "
						+ " and year (agreement.agreetmentDate) = :year "
						+ " and  agreement.member.agent.agentId = :agentId ");

		Query query = getSession().createQuery(str.toString());
		query.setParameter("value", CommonConstant.NOT_DELETED);
		query.setParameter("agentId", agentId);
		query.setParameter("month", month);
		query.setParameter("ackstatus", AgreementConstant.AGREEMENT_STATUS_ACK);// TODO confirm whether ack is what? i
																				// want active agreement
		query.setParameter("terminatestatus", AgreementConstant.AGREEMENT_STATUS_TERMINATED);
		query.setParameter("year", year);
		if (query.list().get(0) == null)
			return 0;
		return new Double(query.list().get(0) + "");
	}

	public List<Agreement> getAgreementForCommOutstanding(String agentId) {
		StringBuffer str = new StringBuffer();
		str.append(" select agreement from Agreement agreement where agreement.isDeleted=:value and "
				+ " agreement.member.agent.agentId=:agentId " + " and agreement.agentCommissionOutstanding>0");

		Query query = getSession().createQuery(str.toString());
		query.setParameter("value", CommonConstant.NOT_DELETED);
		query.setParameter("agentId", agentId);
		return (List<Agreement>) query.list();
	}

	public List<Agreement> getAgreementForCommOverPay(String agentId) {
		StringBuffer str = new StringBuffer();
		str.append(" select agreement from Agreement agreement where agreement.isDeleted=:value and "
				+ " agreement.member.agent.agentId=:agentId  " + "  " + " and agreement.agentCommissionOutstanding<0");

		Query query = getSession().createQuery(str.toString());
		query.setParameter("value", CommonConstant.NOT_DELETED);
		query.setParameter("agentId", agentId);
		return (List<Agreement>) query.list();
	}

	public void deleteAgentCommission(String agentId, int month, int year) {
		String str = " delete from AgentCommission a where a.agent.agentId=:agentId "
				+ " and a.month=:month and a.year=:year  ";

		Query query = getSession().createQuery(str.toString());
		query.setParameter("agentId", agentId);
		query.setParameter("month", month);
		query.setParameter("year", year);
		query.executeUpdate();
	}

}
